
clear
capture log close
set more off



forval i=0/8 {
local j=`i'+1 
clear
use "$dta1\intrvw1`i'\intrvw1`i'\fmli1`j'1.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019

save 201`i'_q1.dta,replace
}


*** 2000-2008 no 2009 reason as before
forval i=0/8 {
local j=`i'+1 
clear
use "$dta1\intrvw0`i'\intrvw0`i'\fmli0`j'1.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019

save 200`i'_q1.dta,replace
}


*** 1995-1998 1994 before no next year
forval i=5/8 {
local j=`i'+1 
clear
use "$dta1\intrvw9`i'\intrvw9`i'\fmli9`j'1.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019

save 199`i'_q1.dta,replace
}


***2010-2019 q2-q4
forval j=0/9 {
forval i=2/4{
clear
use "$dta1\intrvw1`j'\intrvw1`j'\fmli1`j'`i'.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019

save 201`j'_q`i'.dta,replace
}
}



**** 2000-2009 q2-q4

forval j=0/9 {
forval i=2/4{
clear
use "$dta1\intrvw0`j'\intrvw0`j'\fmli0`j'`i'.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019

save 200`j'_q`i'.dta,replace
}
}

***1994-1999 q2-q4

forval j=4/9 {
forval i=2/4{
clear
use "$dta1\intrvw9`j'\intrvw9`j'\fmli9`j'`i'.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019

save 199`j'_q`i'.dta,replace
}
}

*** 19 q1
clear
use "$dta1\intrvw19\intrvw19\fmli201.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019
save 2019_q1.dta,replace

***09 q1 this ele_2019 is only name 

clear
use "$dta1\intrvw09\intrvw09\fmli101.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019
save 2009_q1.dta,replace


** 1999

clear
use "$dta1\intrvw99\intrvw99\fmli001.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019
save 1999_q1.dta,replace

*** 1994
clear
use "$dta1\intrvw94\intrvw94\fmli941.dta"
gen elc=elctrccq+elctrcpq

egen totalweight=sum(finlwt21)
gen total=elc*finlwt21
egen totalall=sum(total)
gen ele_2019=totalall/totalweight
sum ele_2019
save 1994_q1.dta,replace // this only takes a missing q1 slot in 1994  
 *** get annual 
 clear
 scalar sume=0
 forval i=1994/2019{
 forval j=1/4 {
 clear
 use 	`i'_q`j'
 quietly sum ele_2019
 quietly return list 
 scalar q`j'=r(mean)
 
}
quietly scalar sume=q1+q2+q3+q4
 di `i', sume
 } 
 
 

clear

forval i=1/4{
clear
use "$dta1\\intrvw95\intrvw95\fmli95`i'.dta"
drop if fincbtax<=0
_pctile fincbtax, nq(20)

return list

}


clear
forval i=1/4{
clear
use "$dta1\\intrvw95\intrvw95\fmli95`i'.dta"
keep fincbtax elctrccq elctrcpq
drop if fincbtax<=0
gen elc_cost=elctrccq+elctrcpq
drop if elc_cost==0
drop if elc_cost==.
/*
xtile fincbtax_20=fincbtax,nq(20)

tab fincbtax_20

_pctile fincbtax, nq(20)
return list

bysort fincbtax_20: egen costmean=mean(elc_cost)
bysort fincbtax_20: sum(fincbtax)
*/

gen finc=1 if fincbtax<1831
replace finc=2 if fincbtax>=1831 & fincbtax<5599
replace finc=3 if fincbtax>=5599 & fincbtax<7650
replace finc=4 if fincbtax>=7650 & fincbtax<9888 
replace finc=5 if fincbtax>=9888 & fincbtax<12164
replace finc=6 if fincbtax>=12164 & fincbtax<14912 
replace finc=7 if fincbtax>=14912 & fincbtax<17577
replace finc=8 if fincbtax>=17577 & fincbtax<20170
replace finc=9 if fincbtax>=20170 & fincbtax<23715
replace finc=10 if fincbtax>=23715 & fincbtax<26910 
replace finc=11 if fincbtax>=26910 & fincbtax<30807
replace finc=12 if fincbtax>30807 & fincbtax<34913 
replace finc=13 if fincbtax>=34913 & fincbtax<39147
replace finc=14 if fincbtax>=39147 & fincbtax<44250
replace finc=15 if fincbtax>=44250& fincbtax<50325
replace finc=16 if fincbtax>=50325 & fincbtax<56518
replace finc=17 if fincbtax>=56518& fincbtax<64510
replace finc=18 if fincbtax>=64510 & fincbtax<75350 
replace finc=19 if fincbtax>=75350 & fincbtax<98250
replace finc=20 if  fincbtax>=98250

collapse elc_cost fincbtax, by (finc)
rename elc_cost elc_cost_`i'

save 1995_q`i'.dta,replace
}




clear
use 1995_q1
forval i=2/4 {
merge  1:1  finc using 1995_q`i'
drop _merge
}
gen ecost_1995=elc_cost_1+elc_cost_2+elc_cost_3+elc_cost_4
sum fincbtax
gen ecost=int(ecost_1995)

keep finc ecost_1995 
save 1995.dta,replace

*** 2019

clear
use "C:\Users\yqiu16\Desktop\Income_energy\data\intrvw19\intrvw19\fmli191x.dta"
keep fincbtax elctrccq elctrcpq
drop if fincbtax<=0
gen elc_cost=elctrccq+elctrcpq
drop if elc_cost==0
drop if elc_cost==.
drop if fincbtax==.

_pctile fincbtax, nq(20)
return list

gen finc=1 if fincbtax<1831
replace finc=2 if fincbtax>=1831 & fincbtax<5599
replace finc=3 if fincbtax>=5599 & fincbtax<7650
replace finc=4 if fincbtax>=7650 & fincbtax<9888 
replace finc=5 if fincbtax>=9888 & fincbtax<12164
replace finc=6 if fincbtax>=12164 & fincbtax<14912 
replace finc=7 if fincbtax>=14912 & fincbtax<17577
replace finc=8 if fincbtax>=17577 & fincbtax<20170
replace finc=9 if fincbtax>=20170 & fincbtax<23715
replace finc=10 if fincbtax>=23715 & fincbtax<26910 
replace finc=11 if fincbtax>=26910 & fincbtax<30807
replace finc=12 if fincbtax>30807 & fincbtax<34913 
replace finc=13 if fincbtax>=34913 & fincbtax<39147
replace finc=14 if fincbtax>=39147 & fincbtax<44250
replace finc=15 if fincbtax>=44250& fincbtax<50325
replace finc=16 if fincbtax>=50325 & fincbtax<56518
replace finc=17 if fincbtax>=56518& fincbtax<64510
replace finc=18 if fincbtax>=64510 & fincbtax<75350 
replace finc=19 if fincbtax>=75350 & fincbtax<98250
replace finc=20 if  fincbtax>=98250

collapse elc_cost, by (finc)
rename elc_cost elc_cost_1

save 2019_q1.dta,replace




clear
use 2019_q1
forval i=2/4 {
merge  1:1  finc using 2019_q`i'
drop _merge
}
gen ecost_2019=elc_cost_1+elc_cost_2+elc_cost_3+elc_cost_4
  
keep finc ecost_2019 
save 2019.dta,replace

clear
use 1995.dta
merge 1:1 finc using 2019.dta
drop _merge
gen ec_1995=int(ecost_1995)
gen ec_2019=int(ecost_2019)


graph bar ec_1995 ec_2019,over(finc, relabel(1 "0~1831" 2 "1831~5599" 3 "5599~7650" 4 "7650~9888" 5 "9888~12164" ///
6 "12164~14912" 7 "14912~17577" 8 "17577~20170" 9 "20170~23715" 10 "23715~26910" 11 "26910~30807" ///
12 "30807~34913" 13 "34913~39147" 14 "39147~44250" 15 "44250~50325" 16 "50325~56518" 17 "56518~64510" ///
18 "64510~75350" 19 "75350~98250" 20 ">98250")) ytitle("Average electricity costs ($)") ///
b1title("Household income ($)") title("Electricity cost across income groups in 1995 and 2019") blabel(total)
  

  





















  
  
  *** 2019 real income distribution
clear
use "C:\Users\yqiu16\Desktop\Income_energy\data\intrvw19\intrvw19\fmli191x.dta"
keep fincbtax elctrccq elctrcpq
gen elc_cost=elctrccq+elctrcpq
drop if elc_cost==0
drop if elc_cost==.
drop if fincbtax==.

_pctile fincbtax, nq(20)
return list

gen finc=1 if fincbtax<=0
replace finc=2 if fincbtax>0 & fincbtax<1358 
replace finc=3 if fincbtax>=1358 & fincbtax<6356 
replace finc=4 if fincbtax>=6356 & fincbtax<13520 
replace finc=5 if fincbtax>=13520 & fincbtax<18510
replace finc=6 if fincbtax>=18510 & fincbtax<23915
replace finc=7 if fincbtax>=23915 & fincbtax<29084 
replace finc=8 if fincbtax>=29084 & fincbtax<34527
replace finc=9 if fincbtax>=34527 & fincbtax<40274
replace finc=10 if fincbtax>=40274 & fincbtax<46239 
replace finc=11 if fincbtax>=46239 & fincbtax<53767
replace finc=12 if fincbtax>=53767 & fincbtax<61625
replace finc=13 if fincbtax>=61625 & fincbtax<71232
replace finc=14 if fincbtax>=71232 & fincbtax<81125
replace finc=15 if fincbtax>=81125 & fincbtax<95032
replace finc=16 if fincbtax>=95032 & fincbtax<109799
replace finc=17 if fincbtax>=109799 & fincbtax<130932
replace finc=18 if fincbtax>=130932 & fincbtax<155875
replace finc=19 if fincbtax>=155875 & fincbtax<212150
replace finc=20 if fincbtax>=212150

collapse elc_cost, by (finc)
rename elc_cost elc_cost_1

save 2019_q1r.dta,replace


forval i=2/4{
clear
use "C:\Users\yqiu16\Desktop\Income_energy\data\intrvw19\intrvw19\fmli19`i'.dta"
keep fincbtax elctrccq elctrcpq
gen elc_cost=elctrccq+elctrcpq

_pctile fincbtax, nq(20)
return list

gen finc=1 if fincbtax<=0
replace finc=2 if fincbtax>0 & fincbtax<1358 
replace finc=3 if fincbtax>=1358 & fincbtax<6356 
replace finc=4 if fincbtax>=6356 & fincbtax<13520 
replace finc=5 if fincbtax>=13520 & fincbtax<18510
replace finc=6 if fincbtax>=18510 & fincbtax<23915
replace finc=7 if fincbtax>=23915 & fincbtax<29084 
replace finc=8 if fincbtax>=29084 & fincbtax<34527
replace finc=9 if fincbtax>=34527 & fincbtax<40274
replace finc=10 if fincbtax>=40274 & fincbtax<46239 
replace finc=11 if fincbtax>=46239 & fincbtax<53767
replace finc=12 if fincbtax>=53767 & fincbtax<61625
replace finc=13 if fincbtax>=61625 & fincbtax<71232
replace finc=14 if fincbtax>=71232 & fincbtax<81125
replace finc=15 if fincbtax>=81125 & fincbtax<95032
replace finc=16 if fincbtax>=95032 & fincbtax<109799
replace finc=17 if fincbtax>=109799 & fincbtax<130932
replace finc=18 if fincbtax>=130932 & fincbtax<155875
replace finc=19 if fincbtax>=155875 & fincbtax<212150
replace finc=20 if fincbtax>=212150
collapse elc_cost, by (finc)
rename elc_cost elc_cost_`i'

save 2019_q`i'r.dta,replace
}

clear
use 2019_q1r
forval i=2/4 {
merge  1:1  finc using 2019_q`i'r
drop _merge
}
gen ecost_2019r=elc_cost_1+elc_cost_2+elc_cost_3+elc_cost_4

merge 1:1 finc using 2019.dta
drop _merge
gen ec_2019r=int(ecost_2019r)
gen ec_2019=int(ecost_2019)


graph bar ec_2019 ec_2019r,over(finc, relabel(1 "5pct" 2 "10pct" 3 "15pct" 4 "20pct" ///
5 "25pct" 6 "30pct" 7 "135pct" 8 "40pct" 9 "45pct" 10 "50pct" ///
11 "55pct" 12 "60pct" 13 "65pct" 14 "70pct" 15 "75pct" 16 "80pct" ///
17 "85pct" 18 "90pct" 19 ">95pct" 20 "100pct")) ytitle("Average electricity costs ($)") ///
b1title("Household income ($)") title("Electricity cost across income groups in 1995 and 2019") blabel(total)




